/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package QC_Report_Generator_v5;

import java.awt.BorderLayout;
import java.awt.EventQueue;
import java.awt.GridLayout;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.io.BufferedReader;
import java.io.BufferedWriter;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.FileWriter;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Iterator;
import java.util.List;
import java.util.Stack;
import javax.swing.JButton;
import javax.swing.JFileChooser;
import javax.swing.JFrame;
import javax.swing.JPanel;
import javax.swing.JScrollPane;
import javax.swing.JTextArea;
import org.supercsv.io.CsvListReader;
import org.supercsv.prefs.CsvPreference;

/**
 *
 * @author laodiwen
 */
public class QC_Report_Generator_v5 {

    public static void main(String[] args) {

        EventQueue.invokeLater(new Runnable() {

            @Override
            public void run() {
                Frame frame = new Frame();
                frame.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
                frame.setVisible(true);
            }
        });
    }
}

class Frame extends JFrame {

    public static final int DEFAULT_WIDTH = 390;
    public static final int DEFAULT_HEIGHT = 510;

    public Frame() {
        setTitle("QC_Report/Ad_hoc Generator v5.7 - FINAL!");
        setSize(DEFAULT_WIDTH, DEFAULT_HEIGHT);
        setResizable(false);
        component component = new component();
        add(component);
    }
}

class component extends JPanel {

    private JFileChooser fc;
    private File output;
    private File input;
    private FileWriter fstream;
    private FileReader fr;
    private FileReader fr2;
    private BufferedWriter out;
    private BufferedReader in;
    private BufferedReader in2;
    private JButton button;
    private JPanel panel;
    private JTextArea log;
    private ArrayList<String> metrics;
    private ArrayList<String> filters;
    private ArrayList<String> column;
    private ArrayList<String> stepDesc;
    private ArrayList<String> stepExpectedResults;
    private ArrayList<String> stepDesc2;
    private ArrayList<String> stepExpectedResults2;
    private ArrayList<String> straightMove;
    private int q = 0;
    private int z = 0;
    private int y = 0;
    private int n = 0;
    private int x1 = 0;
    private int step = 0;
    private int cycle = 0;
    private String testName;
    private String testCase;
    private int countStraightMove = 0;
    private int countFilters = 0;
    private Calendar cal;
    private SimpleDateFormat dateFormatLocal;
    private String newline;
    private String scriptNum;
    private String reportName;
    private String day;
    private boolean flag1;
    private boolean flag2;
    private boolean flag3;
    private boolean flag4;
    private boolean flag5;
    private boolean flag6;
    private boolean flag7;
    private boolean flag8;
    private boolean flag9;
    private int rowNum;
    private int fColumnNum;
    private String[][] Array2D;
    private CsvListReader parser;
    private int fomularCount;
    private boolean fileExist;
    private int appendCount;
    private String reportType;
    private Parse p;
    private int hold = 0;
    private Stack sk;
    private int move;
    private String tableName;
    private boolean flag10;
    private String sa;
    private boolean flag11;

    public component() {
        flag1 = false;
        flag2 = false;
        flag3 = false;
        flag4 = false;
        flag5 = false;
        flag6 = false;
        flag7 = false;
        flag8 = false;
        flag9 = false;
        flag10 = false;
        flag11 = false;
        panel = new JPanel();
        panel.setLayout(new GridLayout(1, 2));
        metrics = new ArrayList<String>();
        filters = new ArrayList<String>();
        column = new ArrayList<String>();
        stepDesc = new ArrayList<String>();
        stepExpectedResults = new ArrayList<String>();
        stepDesc2 = new ArrayList<String>();
        stepExpectedResults2 = new ArrayList<String>();
        straightMove = new ArrayList<String>();
        dateFormatLocal = new SimpleDateFormat("yyyy-MMM-dd HH:mm:ss");
        newline = "\n" + "\n";
        button = new JButton("Generate");
        panel.add(button);
        button.addActionListener(new GenerateAction());
        fileExist = false;
        Array2D = new String[5000][5000];
        log = new JTextArea(27, 30);
        log.setLineWrap(true);
        log.setEditable(false);
        JScrollPane logScrollPane = new JScrollPane(log);
        add(panel, BorderLayout.CENTER);
        add(logScrollPane, BorderLayout.SOUTH);
        p = new Parse();
        sk = new Stack();
    }

    private class GenerateAction implements ActionListener {

        public void actionPerformed(ActionEvent ae) {
            fc = new JFileChooser();
            int returnVal = fc.showOpenDialog(component.this);
            if (returnVal == JFileChooser.APPROVE_OPTION) {
                input = fc.getSelectedFile();
                returnVal = fc.showSaveDialog(component.this);
                if (returnVal == JFileChooser.APPROVE_OPTION) {
                    output = fc.getSelectedFile();
                    GenerateTemplate();
                }
            }
        }
    }

    private void print2DArray(String[][] list) throws FileNotFoundException {
        int r;
        int c;
        String str = "";
        for (r = 0; r < rowNum; r++) {
            for (c = 0; c < fColumnNum; c++) {
                str += " " + list[r][c];
            }
            str += "\n";
        }
    }

    private void GenerateTemplate() {
        log.setText("");
        reportName = "";
        testCase = "";
        scriptNum = "";
        metrics.clear();
        straightMove.clear();
        countStraightMove = 0;
        countFilters = 0;
        fomularCount = 0;
        try {
            fr = new FileReader(input.getPath());
            in = new BufferedReader(fr);
            try {
                rowNum = 0;
                fColumnNum = 0;
                parser = new CsvListReader(new FileReader(input.getPath()), CsvPreference.EXCEL_PREFERENCE);
                List<String> values = parser.read();
                fColumnNum = values.size();
                while (values != null) {
                    //writeToString(parser.getLineNumber(), values);
                    for (int x = 0; x < values.size(); x++) {
                        Array2D[parser.getLineNumber() - 1][x] = values.get(x);
                        //  System.out.println(QC_Report_Generator_v5[parser.getLineNumber() - 1][x]);
                        if (!Array2D[parser.getLineNumber() - 1][x].equals("")) {
                            rowNum = parser.getLineNumber();
                        }
                    }
                    values = parser.read();
                }

                for (int c = 0; c < fColumnNum; c++) {
                    for (int r = 0; r < rowNum; r++) {
                        //   System.out.println("[" + r + "]" + "[" + c + "]");
                        if (Array2D[r][c].equals("Report Name")) {
                            flag1 = true;
                            flag8 = false;

                        } else if (Array2D[r][c].equals("Metric")) {
                            flag2 = true;
                            flag3 = false;
                            flag9 = false;
                            flag10 = false;
                        } else if (Array2D[r][c].equals("Straight Move")) {
                            flag3 = true;
                            flag2 = false;
                            flag9 = false;
                            flag10 = false;
                        } else if (Array2D[r][c].equals("Script Number")) {
                            flag4 = true;
                        } else if (Array2D[r][c].equals("Test Case ID")) {
                            flag5 = true;
                        } else if (Array2D[r][c].equals("end")) {
                            flag6 = true;
                            flag2 = false;
                            flag3 = false;
                            flag8 = false;
                            flag9 = false;
                            flag10 = false;
                        } else if (Array2D[r][c].equals("Report Type")) {
                            flag8 = true;
                            flag1 = false;
                        } else if (Array2D[r][c].equals("")) {
                            flag1 = false;
                            flag2 = false;
                            flag3 = false;
                            flag4 = false;
                            flag5 = false;
                            flag6 = false;
                            flag7 = false;
                            flag8 = false;
                            flag9 = false;
                            flag10 = false;
                            flag11 = false;

                        } else if (Array2D[r][c].equals("Filter")) {
                            flag9 = true;
                            flag3 = false;
                            flag2 = false;
                        } else if (Array2D[r][c].equals("Table")) {
                            flag10 = true;
                            flag3 = false;
                            flag2 = false;
                        } else if (Array2D[r][c].equals("Subject Area")) {
                            flag11 = true;
                            flag3 = false;
                            flag2 = false;
                        }

                        if (flag1 == true || flag7 == true) {
                            reportName = Array2D[r + 1][c];
                            metrics.clear();
                            straightMove.clear();
                            flag1 = false;
                            flag7 = false;
                        } else if (flag2 == true) {
                            metrics.add(Array2D[r][c]);
                            flag6 = false;
                        } else if (flag10 == true) {

                            tableName = Array2D[r + 1][c];
                            flag10 = false;

                        } else if (flag11 == true) {
                            sa = Array2D[r + 1][c];
                            flag11 = false;
                        } else if (flag8 == true) {
                            reportType = Array2D[r + 1][c];
                            flag8 = false;
                        } else if (flag3 == true) {


                            //report
                            straightMove.add(Array2D[r][c]);   //straightMove = group by / attribute
                            countStraightMove++;
                            flag6 = false;


                            ///////////////               //adhoc
//                            String temp4;
//                            temp4 = Array2D[r + 1][c];
//                            p.toStackAndPostfix(temp4);
//                            straightMove = p.parseList;
//                            for (int z = 0; z < straightMove.size(); z++) {
//                                if (straightMove.get(z).equals("+")
//                                        || straightMove.get(z).equals("-")
//                                        || straightMove.get(z).equals("*")
//                                        || straightMove.get(z).equals("/")
//                                        || straightMove.get(z).equals("COUNT")
//                                        || straightMove.get(z).equals("SUM")
//                                        || straightMove.get(z).equals("DISTINCT")
//                                        || straightMove.get(z).equals("%")) {
//                                    countStraightMove++;
//                                }
//                            }
//                            move = countStraightMove;
//                            flag3 = false;
//                            flag6 = false;
                        } else if (flag4 == true) {
                            scriptNum = Array2D[r + 1][c];
                            flag4 = false;
                        } else if (flag5 == true) {
                            testCase = Array2D[r + 1][c];
                            flag5 = false;
                        } else if (flag6 == true) {
                            //reportTemplate();/////////////////
                            //  adHocTemplate();
                            adHocTemplate2();
                        } else if (flag9 == true) {
                            filters.add(Array2D[r][c]);
                            countFilters++;
                            flag6 = false;
                        }
                    }
                }
            } catch (IOException ex) {
                log.append("Error! Please make sure you have correct input file format" + "\n");
            }
        } catch (FileNotFoundException ex) {
            log.append("File not find: " + input + "\n");
        }
    }

    //Ad hoc Template
    private void adHocTemplate() {
        flag1 = false;
        flag2 = false;
        flag3 = false;
        flag4 = false;
        flag5 = false;
        flag6 = false;
        flag7 = false;
        flag8 = false;
        flag9 = false;
        flag11 = false;

        if (metrics.size() > 0) {
            for (int x = 0; x < metrics.size(); x++) {
                if (metrics.get(x).equals("Metric")) {
                    metrics.remove(x);
                }
            }
        }

        if (filters.size() > 0) {
            for (int x = 0; x < filters.size(); x++) {
                if (filters.get(x).equals("Filter")) {
                    filters.remove(x);
                    countFilters = countFilters - 1;
                }
            }
        }
        String str2, str3, str4;
        int num2 = 0;
        int num3 = 0;

        try {
            int stepCount = countStraightMove + filters.size() + 9;
            fstream = new FileWriter(output, true);
            if (output.exists()) {
                fr2 = new FileReader(output.getPath());
                in2 = new BufferedReader(fr2);
                fileExist = true;
                appendCount = 0;
                String str;
                while ((str = in2.readLine()) != null) {
                    appendCount++;
                }
                in2.close();
            }
            out = new BufferedWriter(fstream);
            step = 0;
            //First five steps description
            stepDesc.add("Run " + scriptNum + ".sql" + " via any DB tool and save the output to an Excel file.");
            stepDesc.add("Login to NII MS report with MS credential");
            stepDesc.add("Select BI DW Reporting and Analytics v0 (SIT)");
            stepDesc.add("Select Share Reports");
            stepDesc.add("Select Report Wizard");

            //First five except result
            stepExpectedResults.add("A list of values from \"" + scriptNum + ".sql" + "\" are generated.");
            stepExpectedResults.add("Login Successfully.");
            stepExpectedResults.add("Main page is loaded.");
            stepExpectedResults.add("Share Reports page is loaded.");
            stepExpectedResults.add("Report Wizard page is loaded.");

            //last 2 steps description
            stepDesc2.add("Run the report and save the output to Excel.");
            stepDesc2.add("\"Compare the ad-hoc output with SQL output from step 1. If a defect is found, highlight "
                    + "both SQL output and Ad-Hoc outputs and creates a defect with both output files from SQL "
                    + "and Ad-Hoc report as well as the SQL script(s).\"");

            //last 2 except result
            stepExpectedResults2.add("Report is generated and the output is saved to Excel.");
            stepExpectedResults2.add("Report output  matchs SQL result.");
            z = 0;
            y = 0;
            n = 0;
            x1 = 0;

            if (fileExist == true) {
                fomularCount = appendCount;
                fileExist = false;
            }
            System.out.println("ID: " + testCase);

            for (int x = 0; x < stepCount; x++) {
                fomularCount++;
                step = x + 1;
                column.add("\"=CONCATENATE(Q" + fomularCount + ",\"\"\\\"\",R" + fomularCount + ",\"\"\\\"\",S" + fomularCount + ")\"");//a
                column.add(testCase);//b
                // column.add("\"=CONCATENATE(Q" + fomularCount + ",\"\"_\"\",B" + fomularCount + ",\"\"_\"\",T" + fomularCount + ")\"");//t
                column.add("\"=CONCATENATE(Q" + fomularCount + ",\"\"_\"\",T" + fomularCount + ")\"");//c
                //column.add(testName);//c
                column.add("\"To verify Microstrategy Ad-hoc metric - " + reportName + " has correct +"
                        + "data from BI MART. To verify the data, the following dimensions are used: " + printMetric(metrics) + "\""); // d
                column.add("P2-High"); //e
                column.add("Step " + step);// f

                if (step < 6) {
                    column.add(stepDesc.get(x));// g
                    column.add(stepExpectedResults.get(x)); //g  h

                } else if (step < 7) {
                    column.add("\"" + "Select " + tableName + " table" + "\"");  ///////////
                    column.add("\"" + "Data for " + tableName + " is loaded" + "\"");  /////////// add tabel
                } else if (step < (7 + move + 1)) {
                    if (countStraightMove > 0) {
                        for (int p = hold; p < straightMove.size(); p++) {
                            if (straightMove.get(p).equals("+")
                                    || straightMove.get(p).equals("-")
                                    || straightMove.get(p).equals("*")
                                    || straightMove.get(p).equals("/")) {
                                if (sk.size() >= 2) {
                                    String g = sk.pop().toString();
                                    String h = sk.pop().toString();
                                    sk.push("Step " + step);
                                    column.add("\"" + "Select " + h + " and " + g + " with " + straightMove.get(p) + " operator" + "\"");
                                    column.add("\"" + "Data for step " + step + " is loaded" + "\"");
                                    hold++;
                                    countStraightMove--;
                                    break;
                                }
                            } else if (straightMove.get(p).equals("COUNT")
                                    || straightMove.get(p).equals("SUM")
                                    || straightMove.get(p).equals("DISTINCT")
                                    || straightMove.get(p).equals("%")) {
                                String g = sk.pop().toString();
                                sk.push("Step " + step);
                                column.add("\"" + "Apply " + straightMove.get(p) + " function to " + g + "\"");
                                column.add("\"" + "Data for step " + step + " is loaded" + "\"");
                                countStraightMove--;
                                hold++;
                                break;
                            } else {
                                sk.push(straightMove.get(p));
                                hold++;
                            }
                        }
                    } else if (!metrics.isEmpty()) {
                        column.add("\"" + "Select the dimension: " + printMetric(metrics) + "\"");//g
                        column.add("\"" + "Data for \"" + printMetric(metrics) + "\" are loaded" + "\""); //h
                    }
                } else if ((step >= (7 + move + 1)) && (step < (7 + move + 1 + filters.size())) && (countFilters > 0)) {
                    column.add("\"Define filter criteria for: " + filters.get(n) + "\""); //g
                    column.add("\"" + filters.get(n) + "\"");//g
                    countFilters--;
                    n++;
                } else {
                    column.add(stepDesc2.get(x1));// g
                    column.add(stepExpectedResults2.get(x1)); //g  
                    x1++;
                }
                column.add("");//i
                column.add("");//j
                column.add("");//q
                column.add("");//l
                column.add("");//m
                column.add("dlao");//n
                column.add("");//o
                column.add("");//p
                column.add("Ad Hoc");//q
                column.add("\"" + reportType + " - " + sa + "\"");//r
                column.add(reportName);//s
                column.add(testCase);//u
                out.append(arrayToString(column) + "\n");
                column.clear();
            }
            stepExpectedResults.clear();
            stepDesc.clear();
            System.out.println("fomular count = " + fomularCount);
            System.out.println();
            out.close();
            in.close();
            cal = Calendar.getInstance();
            log.append("Summary" + newline);
            log.append("Report Type: " + reportType + newline);
            log.append("Report Name: " + reportName + newline);
            log.append("Scripts Number: " + scriptNum + newline);
//            log.append("Test Case: " + testCase + newline);
            str4 = output.getPath();
            log.append("Save to file: " + str4 + "    " + dateFormatLocal.format(cal.getTime()) + newline);
            metrics.clear();
            straightMove.clear();
            filters.clear();
            countStraightMove = 0;
            move = 0;
            countFilters = 0;
            sk.removeAllElements();
            hold = 0;
        } catch (IOException ex) {
            log.setText("");
            cal = Calendar.getInstance();
            log.setText("Error! Please close the opened Excel file    " + dateFormatLocal.format(cal.getTime()) + newline);
        }
    }

    private void adHocTemplate2() {
//        System.out.println(metrics.size());
//        System.out.println(straightMove.size());
        flag1 = false;
        flag2 = false;
        flag3 = false;
        flag4 = false;
        flag5 = false;
        flag6 = false;
        flag7 = false;
        flag8 = false;

        if (filters.size() > 0) {
            for (int x = 0; x < filters.size(); x++) {
                if (filters.get(x).equals("Filter")) {
                    filters.remove(x);
                    countFilters = countFilters - 1;
                }
            }
        }
        if (metrics.size() > 0) {
            for (int x = 0; x < metrics.size(); x++) {
                if (metrics.get(x).equals("Metric")) {
                    metrics.remove(x);
                }
            }
        }
        if (straightMove.size() > 0) {
            for (int x = 0; x < straightMove.size(); x++) {
                if (straightMove.get(x).equals("Straight Move")) {
                    straightMove.remove(x);
                    countStraightMove = countStraightMove - 1;
                }
            }
        }
        String str2, str3, str4;
        int num2 = 0;
        int num3 = 0;

        try {
            int stepCount = straightMove.size() + metrics.size() + filters.size() + 7;
            //  int stepCount = countStraightMove + filters.size() + 9;
            fstream = new FileWriter(output, true);
            if (output.exists()) {
                fr2 = new FileReader(output.getPath());
                in2 = new BufferedReader(fr2);
                fileExist = true;
                appendCount = 0;
                String str;
                while ((str = in2.readLine()) != null) {
                    appendCount++;
                }
                in2.close();
            }
            out = new BufferedWriter(fstream);
            step = 0;
            //First five steps description
            stepDesc.add("Run " + scriptNum + ".sql" + " via any DB tool and save the output to an Excel file.");
            stepDesc.add("Login to NII MS report with MS credential");
            stepDesc.add("Select BI DW Reporting and Analytics v0 (SIT)");
            stepDesc.add("Select Share Reports");
            stepDesc.add("Select Report Wizard");

            //First five except result
            stepExpectedResults.add("A list of values from \"" + scriptNum + ".sql" + "\" are generated.");
            stepExpectedResults.add("Login Successfully.");
            stepExpectedResults.add("Main page is loaded.");
            stepExpectedResults.add("Share Reports page is loaded.");
            stepExpectedResults.add("Report Wizard page is loaded.");

            //last 2 steps description
            stepDesc2.add("Run the report and save the output to Excel.");
            stepDesc2.add("Compare the ad-hoc output with SQL output from step 1. If a defect is found, highlight "
                    + "both SQL output and Ad-Hoc outputs and creates a defect with both output files from SQL "
                    + "and Ad-Hoc report as well as the SQL script(s).");

            //last 2 except result
            stepExpectedResults2.add("Report is generated and the output is saved to Excel.");
            stepExpectedResults2.add("Report output  matchs SQL result.");
            z = 0;
            y = 0;
            n = 0;
            x1 = 0;

            if (fileExist == true) {
                fomularCount = appendCount;
                fileExist = false;
            }
            System.out.println("ID: " + testCase);

            for (int x = 0; x < stepCount; x++) {
                fomularCount++;
                step = x + 1;

                if (step == 1) {
                    column.add("\"=CONCATENATE(Q" + fomularCount + ",\"\"\\\"\",R" + fomularCount + ",\"\"\\\"\",S" + fomularCount + ")\"");//a
                    column.add(testCase);//b
                    // column.add("\"=CONCATENATE(Q" + fomularCount + ",\"\"_\"\",B" + fomularCount + ",\"\"_\"\",T" + fomularCount + ")\"");//t
                    column.add("\"=CONCATENATE(Q" + fomularCount + ",\"\"_\"\",T" + fomularCount + ")\"");//c
                    //column.add(testName);//c
                    column.add("\"To verify Microstrategy Ad-hoc metric - " + reportName + " has correct +"
                            + "data from BI MART. To verify the data, the following dimensions are used: " + printMetric(metrics) + "\""); // d
                    column.add("P2-High"); //e
                    column.add("Step " + step);// f

                    if (step < 6) {
                        column.add(stepDesc.get(x));// g
                        column.add(stepExpectedResults.get(x)); //g  h

                    } else if (step < (6 + straightMove.size() + metrics.size())) {
                        if (countStraightMove > 0) {
                            column.add("\"Select " + straightMove.get(z) + " from Attribute list\"");
                            column.add("\"" + straightMove.get(z) + " is loaded\"");
                            z++;
                            countStraightMove--;
                        } else {
                            column.add("\"Select " + metrics.get(y) + " from Metrics list\"");
                            column.add("\"" + metrics.get(y) + " is loaded\"");
                            y++;
                        }
                    } else if ((step < (6 + straightMove.size() + metrics.size() + filters.size())) && (countFilters > 0)) {

                        column.add("\"" + "Set the filter: " + filters.get(n) + " in attribute filter list\"");//g
                        column.add("\" Filter is set to " + filters.get(n) + "\""); //h
                        n++;
                        countFilters--;
                    } else {
                        if (x1 < 1) {
                            column.add("\"" + stepDesc2.get(x1) + " And do the calculation: " + tableName + "\"");// g
                            column.add("\"" + stepExpectedResults2.get(x1) + "\""); //g  
                            x1++;
                        } else {
                            column.add("\"" + stepDesc2.get(x1) + "\"");// g
                            column.add("\"" + stepExpectedResults2.get(x1) + "\""); //g  
                        }
                    }
                    column.add("");//i
                    column.add("");//j
                    column.add("");//q
                    column.add("");//l
                    column.add("");//m
                    column.add("dlao");//n
                    column.add("");//o
                    column.add("");//p
                    column.add("Ad Hoc");//q
                    column.add("\"" + reportType + " - " + sa + "\"");//r
                    column.add(reportName);//s
                    column.add(testCase);//u
                    out.append(arrayToString(column) + "\n");
                    column.clear();
                } else {
                    column.add("");//a
                    column.add("");//b
                    // column.add("\"=CONCATENATE(Q" + fomularCount + ",\"\"_\"\",B" + fomularCount + ",\"\"_\"\",T" + fomularCount + ")\"");//t
                    column.add("");//c
                    //column.add(testName);//c
                    column.add(""); // d
                    column.add(""); //e
                    column.add("" );// f

                    if (step < 6) {
                        column.add(stepDesc.get(x));// g
                        column.add(stepExpectedResults.get(x)); //g  h

                    } else if (step < (6 + straightMove.size() + metrics.size())) {
                        if (countStraightMove > 0) {
                            column.add("\"Select " + straightMove.get(z) + " from Attribute list\"");
                            column.add("\"" + straightMove.get(z) + " is loaded\"");
                            z++;
                            countStraightMove--;
                        } else {
                            column.add("\"Select " + metrics.get(y) + " from Metrics list\"");
                            column.add("\"" + metrics.get(y) + " is loaded\"");
                            y++;
                        }
                    } else if ((step < (6 + straightMove.size() + metrics.size() + filters.size())) && (countFilters > 0)) {

                        column.add("\"" + "Set the filter: " + filters.get(n) + " in attribute filter list\"");//g
                        column.add("\" Filter is set to " + filters.get(n) + "\""); //h
                        n++;
                        countFilters--;
                    } else {
                        if (x1 < 1) {
                            column.add("\"" + stepDesc2.get(x1) + " And do the calculation: " + tableName + "\"");// g
                            column.add("\"" + stepExpectedResults2.get(x1) + "\""); //g  
                            x1++;
                        } else {
                            column.add("\"" + stepDesc2.get(x1) + "\"");// g
                            column.add("\"" + stepExpectedResults2.get(x1) + "\""); //g  
                        }
                    }
                    column.add("");//i
                    column.add("");//j
                    column.add("");//q
                    column.add("");//l
                    column.add("");//m
                    column.add("");//n
                    column.add("");//o
                    column.add("");//p
                    column.add("");//q
                    column.add("");//r
                    column.add("");//s
                    column.add("");//u
                    out.append(arrayToString(column) + "\n");
                    column.clear();
                }

            }
            stepExpectedResults.clear();
            stepDesc.clear();
            System.out.println("fomular count = " + fomularCount);
            System.out.println();
            out.close();
            in.close();
            cal = Calendar.getInstance();
            log.append("Summary" + newline);
            log.append("Report Type: " + reportType + newline);
            log.append("Report Name: " + reportName + newline);
            log.append("Scripts Number: " + scriptNum + newline);
//            log.append("Test Case: " + testCase + newline);
            str4 = output.getPath();
            log.append("Save to file: " + str4 + "    " + dateFormatLocal.format(cal.getTime()) + newline);
            metrics.clear();
            straightMove.clear();
            filters.clear();
            countStraightMove = 0;
            move = 0;
            countFilters = 0;
            sk.removeAllElements();
            hold = 0;
        } catch (IOException ex) {
            log.setText("");
            cal = Calendar.getInstance();
            log.setText("Error! Please close the opened Excel file    " + dateFormatLocal.format(cal.getTime()) + newline);
        }
    }
    // Report Template

    private void reportTemplate() {
        System.out.println(metrics.size());
        System.out.println(straightMove.size());
        flag1 = false;
        flag2 = false;
        flag3 = false;
        flag4 = false;
        flag5 = false;
        flag6 = false;
        flag7 = false;
        flag8 = false;
        if (metrics.size() > 0) {
            for (int x = 0; x < metrics.size(); x++) {
                if (metrics.get(x).equals("Metric")) {
                    metrics.remove(x);
                }
            }
        }
        if (straightMove.size() > 0) {
            for (int x = 0; x < straightMove.size(); x++) {
                if (straightMove.get(x).equals("Straight Move")) {
                    straightMove.remove(x);
                    countStraightMove = countStraightMove - 1;
                }
            }
        }
        String str2, str3, str4;
        int num2 = 0;
        int num3 = 0;

        try {
            int stepCount = straightMove.size() + metrics.size() + 3;
            fstream = new FileWriter(output, true);

            if (output.exists()) {
                fr2 = new FileReader(output.getPath());
                in2 = new BufferedReader(fr2);
                fileExist = true;
                appendCount = 0;
                String str;
                while ((str = in2.readLine()) != null) {
                    appendCount++;
                }
                in2.close();
            }

            out = new BufferedWriter(fstream);

            for (int c2 = 0; c2 < 3; c2++) {

                q = 0;
                step = 0;
                cycle = c2 + 1;
                stepDesc.add("Execute " + reportName + " test SQL Script (Script " + scriptNum + "C" + cycle + " ) output  (in Excel)  and identify cycle "
                        + cycle + " records.");
                stepDesc.add("Login to MS Reports folder via web browser. Select " + reportName);
                stepDesc.add("In the Date prompts: enter Cycle"
                        + cycle + " date in both From and To date fields. Keep all the other default prompts. And execute the report.");

                stepExpectedResults.add("A list of metrics and values from " + scriptNum + "C" + cycle + " SQL script are generated.");
                stepExpectedResults.add("Login Successfully.");
                stepExpectedResults.add("Report accept both date entries.");
                if (cycle == 1) {
                    day = "Single Day";
                } else if (cycle == 2) {
                    day = "Multi Day";
                } else {
                    day = "Cross Month";
                }
                for (int v = 0; v < 2; v++) {
                    q = v + 1;
                    z = 0;
                    y = 0;

                    if (cycle == 1 && q == 1) {
                        testName = "SingleDayDefaultValues";
                    } else if (cycle == 1 && q == 2) {
                        testName = "SingleDayOneParater";
                    } else if (cycle == 2 && q == 1) {
                        testName = "MultiDayPageAll";
                    } else if (cycle == 2 && q == 2) {
                        testName = "MultisSingleDayPageBy";
                    } else if (cycle == 3 && q == 1) {
                        testName = "AMonthPageAll";
                    } else if (cycle == 3 && q == 2) {
                        testName = "AMonthSinglePageBy";
                    }

                    if (fileExist == true) {
                        fomularCount = appendCount;
                        fileExist = false;
                    }
                    for (int x = 0; x < stepCount; x++) {
                        fomularCount++;
                        step = x + 1;
                        column.add("\"=CONCATENATE(Q" + fomularCount + ",\"\"\\\"\",R" + fomularCount + ",\"\"\\\"\",S" + fomularCount + ")\"");//a
                        column.add(testCase + "00" + cycle + " - 0" + q);//b
                        column.add("\"=CONCATENATE(Q" + fomularCount + ",\"\"_\"\",B" + fomularCount + ",\"\"_\"\",T" + fomularCount + ")\"");//t
//                                                column.add(testName);//c
                        column.add("Verification of the following: 1.Metric data is correct for Cycle "
                                + cycle + " data " + "( " + day + " )" + ". "
                                + "The Test script for this test cases is " + scriptNum + "C" + cycle + "; 2. The totals match the detail."
                                + "The metrics in the Report are: " + printMetric(metrics)); // d
                        column.add("P2-High"); //e
                        column.add("Step " + step);// f

                        if (step < 4) {
                            column.add(stepDesc.get(x));// g
                            column.add(stepExpectedResults.get(x)); //g  h
                        } else {
                            if (countStraightMove > 0) {
                                column.add("Compare " + straightMove.get(z) + " in the report with Step 1's SQL result for " + straightMove.get(z)
                                        + ". These two numbers is matching");//g
                                column.add(straightMove.get(z) + " from report matches SQL results"); //h
                                countStraightMove--;
                                z++;
                            } else if (metrics.isEmpty() == false) {
                                column.add("Compare the Total of " + metrics.get(y) + " in the report with Step 1's SQL result for " + metrics.get(y)
                                        + ". These two numbers is matching");//g
                                column.add(metrics.get(y) + " from report matches SQL results"); //h
                                y++;
                            }
                        }
                        column.add("");//i
                        column.add("");//j
                        column.add("");//q
                        column.add("");//l
                        column.add("");//m
                        column.add("");//n
                        column.add("");//o
                        column.add("");//p
                        column.add("Reporting");//q
                        column.add("R - " + reportType);//r
                        column.add(reportName);//s
//                                                column.add("\"=CONCATENATE(Q" + fomularCount + ",\"\"_\"\",B" + fomularCount + ",\"\"_\"\",C" + fomularCount + ")\"");//t
                        column.add(testName);//t
                        column.add("Design");//u

                        out.append(arrayToString(column) + "\n");

                        column.clear();
                    }
                    countStraightMove = straightMove.size();
                }
                stepExpectedResults.clear();
                stepDesc.clear();
            }
            System.out.println("fomular count = " + fomularCount);
            out.close();
            in.close();
            cal = Calendar.getInstance();
            log.append("Summary" + newline);
            log.append("Report Type: " + reportType + newline);
            log.append("Report Name: " + reportName + newline);
            log.append("Scripts Number: " + scriptNum + newline);
            log.append("Test Case: " + testCase + newline);
            if (straightMove.isEmpty() == false) {
                Iterator itr2 = straightMove.iterator();
                log.append("Total Straight Move:" + "\n");
                while (itr2.hasNext()) {
                    num3++;
                    str3 = itr2.next() + "\n";
                    log.append(num3 + ". " + str3);
                }
                log.append("\n");
            }

            if (metrics.isEmpty() == false) {
                Iterator itr = metrics.iterator();
                log.append("Total Metrics:" + "\n");
                while (itr.hasNext()) {
                    num2++;
                    str2 = itr.next() + "\n";
                    log.append(num2 + ". " + str2);
                }
                log.append("\n");
            }
            str4 = output.getPath();
            log.append("Save to file: " + str4 + "    " + dateFormatLocal.format(cal.getTime()) + newline);
            metrics.clear();
            straightMove.clear();
            countStraightMove = 0;

        } catch (IOException ex) {
            log.setText("");
            cal = Calendar.getInstance();
            log.setText("Error! Please close the opened Excel file    " + dateFormatLocal.format(cal.getTime()) + newline);
        }
    }

    private String arrayToString(ArrayList<String> a) {
        String str = "";
        Iterator itr = a.iterator();
        while (itr.hasNext()) {
            str = str + itr.next() + ",";
        }
        return str;
    }

    private String printMetric(ArrayList<String> list) {
        String str = "";
        int num = 0;
        Iterator itr = list.iterator();
        while (itr.hasNext()) {
            num++;
            str = str + num + ". " + itr.next() + "; ";
        }
        return str;
    }
}
